So this cialis pills wholesale is your chance to see which one comes out on top. It works price of viagra competently with all the support of one s efficient factor called Vardenafil, which gets familiar in combating with erectile dysfunction. There are a group of people who have taken viagra prescription this drug on a consistent basis and have felt the difference. Impotence Treatments Spe50mg viagra sale t in Delhi is here to discuss, what exactly adult literature is and why it is causing a patient’s pain and dysfunction and is the most direct way in which it occurs is through lowering of blood pressure.
How to Create a Crosstab Query for Customer type, Product, and Category
A crosstab query is a type of select query. A crosstab query calculates a sum, average, and other aggregate functions, and then groups the results into two sets of values—one down the side of the datasheet (called Row) and the other across the datasheet (called Column). You can also think of it as the two dimensions of a table.
Methods For Creating Your Crosstab Query
- Using the Crosstab Query Wizard The Crosstab Query Wizard is usually the fastest and easiest way to create a crosstab query. It does most of the work for you, but there are a few options that the wizard does not offer.
- Using Design view The Design view allows you more control over your query design. It supports the features that are not available in the wizard.
- Writing a Query in SQL view You can write a crosstab query in SQL view; if you prefer. However, you cannot specify parameter data types by using SQL view. You can specify the parameter data type by modifying your query in Design view.
In this how-to, I will show you how to create a Crosstab Query by using Crosstab Query Wizard, and how to edit it with the Query Design View.
Creating Query for Crosstab Query
In this how-to, I will create a crosstab query that requires many fields from different tables. So, we need to create a universal query that collects many fields from more than one table. I will add Category, Products, Orders, tbl_Customer, and tbl_Customer_Type table into a query design. Then, I will add some fields as lists below on the Figure 1. I will also create more fields such as MonthNo, Month, and Year from the Order Date field.
MonthNo: Format([Order Date], “mm”) result of 5/1/2016 = 05
Month: Format([Order Date], “mmm”) result of 5/1/2016 = May
Year: Format([Order Date], “yyyy”) result of 5/1/2016 = 2016
Figure 1 (All Orders)
Datasheet view of All Orders query design view, above.
Example #1 (Each Customer Type with Number of Order for each Category)
Step by Step of Creating a Crosstab Query Above on Example #1
Step #1 Click Query Wizard under the Create Menu to open a Crosstab query wizard
Step #2 Select Crosstab Query Wizard then click OK
Step #3 Select All Orders query then click Next>
Step #4 Select Custoemer_Type field to be a Row Heading as shown below. Then click Next>
Step #5 Select Category field to be a Column Heading as shown below. Click Next>
Step #6 Select OrderNo field and select Count function as shown below. This means we want the OrderNo field to be calculated with a count function to get the number of orders for each Customer type (row) and each product category (column). We can also check “Yes” to include row sums. Then Click Next>.
Step #7 Save query name as All Orders_Crosstab or leave it as tbl_Customer_Crosstab. Click Finish to view the query.
Datasheet view of All Orders_Crosstab query
The Customer_Type field (Row Heading) will list the type of customers like Business, Government, Individual, and Non-Profit. The Category field will display the category type as the Column Heading like Candy, Drinks, FoodStuff, Snacks, Canned, and Others. It also shows the total number of order for each customer type as we check “Yes” to include row sums.
The query design view of All Orders_Crosstab query above
For more understanding about Crosstab query, I will explain briefly on the Crosstab query shown below compare to the query view above.
- Customer_Type field is a Row Heading of crosstab and calculated by Group by. It displays a row of each customer type with no duplicate on each customer type like business, Government, Individual, and Non-Profit.
- The Total of OrderNo field is a calculated field (count from Row Total) for the Row Heading of crosstab. It is showing the total number of order for each customer type. For example, business type has 2 orders in total.
- Category field is a Column Heading of crosstab and calculated by Group by. It displays each category as a column heading such as Drinks, FoodStuff, etc.
- The OrderNo field is a value field of Customer type (Row Heading) and Category (Column Heading). This field is a required field for the Crosstab query. The total number of order for each category is calculated (count of OrderNo) for each customer type and each category. It is showing the detail for each customer type. For example, business type has 2 orders in total which come from 1 order from Drinks category and 1 order from FoodStuff category.
Example #2 (Each Month with Order Amount for each Category)
The Month field (Row Heading) will list the Month of the year like January, February, March, etc. The Category field will display the category type as the Column Heading like Candy, Drinks, FoodStuff, Snacks, Canned, and Others with its total order amount for each month. It also shows the total number of order for each month.
The query design view of All Orders_Crosstab by Month query above
The query design below is a little bit different from the query design for All Orders_Crosstab above. We can edit that query by the step below. The Total of OrderNo field (count of Row Heading) and the Category field (Column Heading) are not changed. There are 4 fields have changed as explained below.
- MonthNo field does not display in the query view above. The month number like 05 for 5/1/16 is the data displayed on the field and is used to sort the month by number 01- 12. We will see the month displayed on the query view above as February – November in the month order, not alphabetically order.
- Month field is a Row Heading of crosstab query that we change from the Customer Type field. The Month field is displayed in the month order as sorted by the MonthNo field on number 1.
- Total Order field is a calculated field and displayed as a column. It calculates the total order amount (Sum) for each month. It is optional field.
- Total field is a calculated value field of Row Heading (Month) and Column Heading (Category). This field is a required field for a Crosstab query. The total number of order for each category is calculated (Sum of Total) for each month. For example, May month has 4 orders in total of $106 (Per Figure 1, 5/1/16 -Candy $28, 5/4/16-Candy $18, 5/4/16- FoodStuff $46, and 5/3/16 Drinks $14) from 3 categories (Candy $46, FoodStuff $46, and Drinks $14).
Example #3 (Product with Order Amount for each Month)
The ProductID field (Row Heading) will list the products. The Month field will be displayed as the Column Heading like Jan, Feb, Mar, etc. The Total field is a calculated field that displays the total number of order for each product and each month.
The query design view of Product_Crosstab query above
The query design below is a little bit different from the previous query design above. We can edit that query by the step below. The Total Order of Total field (Sum of Total for Row Heading) and the Total field (Sum of Total for Column Heading) are not changed. There are 2 fields have changed as explained below.
- Delete the MonthNo field from previous design.
- Change Month field to ProductID field for Row Heading.
- Change Category field to Month field for the Column Heading.
- The Total field is a required field for a Crosstab query. This Total field calculates the total (sum) of the order amount of each Product (Row Heading) and Month (Column Heading).
Auto Report of Product Crosstab query above
You can change or move the Month Column in the correct order as you want.
